--:setvar logfilename C:\inetpub\logs\LogFiles\W3SVC1\u_ex130615.log
--:setvar bcpfmtfile C:\Users\mikesu\Documents\WebLogImport\Weblogimport.fmt
--------------------------------------------------------------------------------------------------------------------
-- NOTE: if this file is executed in the SSMS query window, you must enable SQLCMD mode for it to execute properly.  
-- You will also need to uncomment the :setvar commands at the top of this script and alter the variables for the 
-- files you want to use.  
--------------------------------------------------------------------------------------------------------------------
-- set nocount on 
use[weblogs]
Print 'Web Log Import Script'
Print 'Version 2.0'
Print '-----------------------'
-- Setup the variables that will be used
declare @sqlstring nvarchar(max) = '',  -- Var to hold the sql statement for building and importing
		@filename nvarchar(255) ='',
		@importime datetime=getdate(),
		@importID bigint
DECLARE @fieldtable TABLE (Rownumber int,Cstring NVARCHAR(MAX)) -- table for the .fmt file column data
DECLARE @ResetTable TABLE (Rownumber int identity, cstring nvarchar(max)) -- table to hold all rows that start with # (metadata)
select @filename= '##'+reverse(left(reverse('$(logfilename)'),charindex('\',reverse('$(logfilename)'))-1));

print 'Recording log file informaion'
insert INTO iislogging.LogFiles(logfilename,importdate,completed ) 
values ('$(logfilename)', @importime, 0);
SELECT @importid = LogFile_ID from iislogging.logfiles where importdate = @importime;
-- select @importid, * from iislogging.logfiles;
Print '-----------------------'
print 'Importing metadata' ;
with CTE1 as (select c1 from openrowset(BULK '$(logfilename)', FORMATFILE='$(bcpfmtfile)') as T1(C1) where c1 like '#%') 
insert @Resettable select * from CTE1 
--select * from @resetTable 
Print '-----------------------'
print 'Recording IIS Reset data'
insert into iislogging.iisresets (LogFile_ID, ResetDate) 
Select @importid,right(cstring,len(cstring)-charindex(':',cstring)) from @resetTable where cstring like '#D%';

-- select * from iislogging.iisresets

-- How many UNIQUE #fields rows are in the file (created at every IIS reset)
if (select count(*) from (select distinct cstring from @resettable where cstring like '#F%') as t1 ) = 1 
begin 
	Print '-----------------------'
	print 'processing the file' 
	 -- Get the list of fields, parse it and convert it to a table 
	insert @fieldtable
	select rownumb,Colname 
	from iislogging.fnWebgetcolnames((select distinct cstring from @resettable as T1 where cstring like '#F%'))
	where colname <>'#Fields:' 
	delete from @fieldtable where cstring = ''
	-- select * from @fieldtable	

	-- Build the import table. 
	select @sqlstring = 'Create Table ['+@filename+'] ('

	select @sqlstring = @sqlstring + '['+f.cstring+'] nvarchar (max) NULL,' 
	from @fieldtable F 
	order by f.rownumber asc

	select @sqlstring = @sqlstring +') WITH (DATA_COMPRESSION = PAGE);'
	Print '-----------------------'
	print 'Building import table'
	print @sqlstring 
	exec (@sqlstring) 
	
	Print '-----------------------'
	print 'Importing the data' 
	exec ('bulk insert ['+ @filename +'] from ''$(logfilename)'' with ( firstrow = 2, fieldterminator = '' '', rowterminator = ''\n'', maxerrors = 10);')
	print 'Deleting rows that begin with #' 
	select @sqlstring = 'delete from ['+ @filename +'] where left(date,1)=''#'''
	exec (@sqlstring)

	--Build the command to insert from the @filename table to the WebLog table. 
	select @sqlstring = 'insert into [iislogging].[Weblog] (logfile_id,'

	select @sqlstring = @sqlstring + '['+ f.cstring+'],'
	from @fieldtable F 
	order by f.rownumber asc
	
	select @sqlstring=@sqlstring + ') select '+ltrim(str(@importID))+','

	select @sqlstring = @sqlstring + 'convert('+c.data_type + 
			case c.data_type 
			when 'nvarchar' then ' ('+convert(varchar(8),COL_LENGTH('iislogging.Weblog',f.cstring)/2)+')'
			when 'time'then ' (0)'
			else ''  
			end+  
			',['+c.column_name+']),' 
	from @fieldtable F left join information_schema.columns c on f.cstring = c.column_name 
	where c.table_name='WebLog'

	select @sqlstring = @sqlstring + ' from ['+@filename+'];'
	select @sqlstring = replace(@sqlstring,'],)','])')
	select @sqlstring = replace(@sqlstring,']), ',']) ')
	Print '-----------------------'
	print 'Copying data to WebLog table' 
	print @sqlstring
	exec (@sqlstring)

--	select @@rowcount as rowsimported
	Print '-----------------------'
	print 'deleting the import table' 
	select @sqlstring = 'drop table ['+ @filename+']'
	exec (@sqlstring) 
	Print '-----------------------'
	Print 'Completed'
	update iislogging.logfiles set completed = 1 where logfile_id = @importid
end 
else 
begin
	print 'cannot import files that have multiple #FIELDS definitions' 
end

--truncate table iislogging.Weblog
-- select * from iislogging.Weblog
-- select * from iislogging.logfiles